package com.guochenglang.www.util.connect;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Jdbc的详细封装(insert,update,delete,select)（不是很会a）
 *
 * @author 郭程朗
 */
public class JdbcUtil {
    /**
     * 创建静态连接池对象
     */
    private static ConnectManager pool = ConnectManager.getPool();

    public JdbcUtil() {

    }


    /**
     * 插入信息
     *
     * @param comfort 插入的信息
     * @param table   表名
     * @return 添加数据后的自增主键
     */
    public static <T> int insert(String table, T comfort) throws SQLException, IllegalAccessException, InterruptedException {
        //通过sql的构建器获得sql语句
        String sql = SqlBuilder.insertSql(table, comfort);
        //通过连接池获取连接
        Connection conn = pool.getConnection();
        PreparedStatement pre = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
        //计数器，表示第几个占位符
        int count = 1;
        Class<?> aClass = comfort.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        //通过键值对对占位符进行赋值
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            Object value = declaredField.get(comfort);
            //value值为空代表非添加的数据
            if (value != null) {
                pre.setObject(count, value);
                count++;
            }
        }
        pre.executeUpdate();
        int getGeneratedKey = -1;
        //获取添加数据后的自增主键
        ResultSet resultSet = pre.getGeneratedKeys();
        if (resultSet.next()) {
            getGeneratedKey = resultSet.getInt(1);
        }
        //判断是否为自动提交
        if (conn.getAutoCommit()) {
            pool.returnConnection();
        }
        //释放资源
        pool.releaseAll(null, pre, resultSet);
        return getGeneratedKey;
    }


    /**
     * 删除语句
     *
     * @param table      表名
     * @param conditions 条件
     */
    public static <T> int delete(String table, T conditions) throws SQLException, IllegalAccessException, InterruptedException {
        //通过sql构建器获得sql语句
        String sql = SqlBuilder.deleteSql(table, conditions);
        System.out.println(sql);
        //计数器，表示第几个占位符
        int count = 1;
        Connection conn = pool.getConnection();
        PreparedStatement pre = conn.prepareStatement(String.valueOf(sql));
        Class<?> aClass = conditions.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            Object value = declaredField.get(conditions);
            //value值为空代表非删除的条件
            if (value != null) {
                pre.setObject(count, value);
                count++;
            }
        }
        //回收资源
        int num = pre.executeUpdate();
        //判断是否为自动提交
        System.out.println(conn.getAutoCommit());
        if (conn.getAutoCommit()) {
            pool.returnConnection();
        }
        pool.releaseAll(null, pre, null);
        return num;
    }


    /**
     * 自定义sql语句，通过键值对应来赋值
     *
     * @param table      表名
     * @param set        修改的数据
     * @param conditions 条件
     */
    public static <T> int update(String table, Object set, T conditions) throws SQLException, IllegalAccessException, InterruptedException {
        //通过sql构建器获得sql语句
        String sql = SqlBuilder.updateSql(table, set, conditions);
        //ConnectManager pool = new ConnectManager();
        Connection conn = pool.getConnection();
        PreparedStatement pre = conn.prepareStatement(sql.toString());
        //计数器，表示第几个占位符
        int count = 1;
        Class<?> aClass = set.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        //对占位符进行赋值
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            Object value = declaredField.get(set);
            if (value != null) {
                pre.setObject(count, value);
                count++;
            }
        }
        Class<?> aClass1 = conditions.getClass();
        Field[] declaredFields1 = aClass1.getDeclaredFields();
        for (Field field : declaredFields1) {
            field.setAccessible(true);
            Object value = field.get(conditions);
            //value值为空代表非赋值数据
            if (value != null) {
                pre.setObject(count, value);
                count++;
            }
        }

        int num = pre.executeUpdate();
        //判断是否为自动提交
        if (conn.getAutoCommit()) {
            pool.returnConnection();
        }
        pool.releaseAll(null, pre, null);
        return num;
    }


    /**
     * 搜索sql
     *
     * @param table      表名
     * @param conditions 搜索条件
     * @param <T>        泛型，用什么封装条件就返回什么类的集合
     * @return 数据集合
     */
    public static <T> ArrayList<T> selectMax(String table, T conditions) throws SQLException, IllegalAccessException, InstantiationException, InterruptedException {
        String sql = SqlBuilder.selectSqlMax(table, conditions);
        //从数据库获取信息
        Connection conn = pool.getConnection();
        PreparedStatement pre = conn.prepareStatement(sql);
        //计数器，表示第几个占位符
        int count = 1;
        //索引，表示select集合的索引，通过获取索引的元素进行赋值
        int index = 0;
        //获得条件类的字节码文件
        Class<?> aClass = conditions.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        //对占位符进行赋值
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            Object value = declaredField.get(conditions);
            //value值为空代表非条件，赋值的是条件
            if (value != null && value != "") {
                pre.setObject(count, value);
                count++;
            }
        }
        ResultSet resultSet = pre.executeQuery();
        //判断是否为自动提交
        if (conn.getAutoCommit()) {
            pool.returnConnection();
        }

        ArrayList<T> select = new ArrayList<>();
        while (resultSet.next()) {
            //将类实例化
            T value = (T) aClass.newInstance();
            //遍历filed，对类进行赋值
            for (Field declaredField : declaredFields) {
                declaredField.setAccessible(true);
                String name = declaredField.getName();
                //将java命名变成sql命名
                Object tValue = resultSet.getObject(SqlBuilder.determindName(name));
                declaredField.set(value, tValue);
            }
            //赋值完后添加进集合中
            select.add(value);
        }
        synchronized (JdbcUtil.class) {
            pool.releaseAll(null, pre, resultSet);
        }
        return select;
    }

    public static <T> ArrayList<T> selectByLimit(String table, int page, int num, T conditions, String order, String mode) throws IllegalAccessException, InterruptedException, SQLException, InstantiationException {
        String sql = SqlBuilder.selectByLimit(table, page, num, conditions, order, mode);
        //打印sql语句查验
        //从数据库获取信息
        Connection conn = pool.getConnection();
        PreparedStatement pre = conn.prepareStatement(sql);
        //计数器，表示第几个占位符
        int count = 1;
        //索引，表示select集合的索引，通过获取索引的元素进行赋值
        int index = 0;
        Class<?> aClass = conditions.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        //对占位符进行赋值
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            Object value = declaredField.get(conditions);
            if (value != null && value != "") {
                pre.setObject(count, value);
                count++;
            }
        }
        ResultSet resultSet = pre.executeQuery();
        //判断是否为自动提交
        if (conn.getAutoCommit()) {
            pool.returnConnection();
        }

        ArrayList<T> select = new ArrayList<>();
        while (resultSet.next()) {
            T value = (T) aClass.newInstance();
            for (Field declaredField : declaredFields) {
                declaredField.setAccessible(true);
                String name = declaredField.getName();
                Object tValue = resultSet.getObject(SqlBuilder.determindName(name));
                declaredField.set(value, tValue);
            }
            select.add(value);
        }
        synchronized (JdbcUtil.class) {
            pool.releaseAll(null, pre, resultSet);
        }
        return select;
    }
}
